CALL SYSADMIN.refreshMatView(viewname=>'schema.matview', invalidate=>true)
Internal materialization creates Teiid temporary tables to hold the materialized table. While these tables are not fully durable, they perform well in most circumstances and the data is present at each Teiid instance which removes the single point of failure and network overhead of an external database. Internal materialization also provides more built-in facilities for refreshing and monitoring.
The Cache Hint, when used in the context of an internal materialized view transformation query, provides the ability to fine tune the materialized table. The pref_mem option also applies to internal materialized views. Internal table index pages already have a memory preference, so the perf_mem option indicates that the data pages should prefer memory as well.
All internal materialized view refresh and updates happen atomically. Internal materialized views support READ_COMMITTED (used also for READ_UNCOMMITED) and SERIALIZABLE (used also for REPEATABLE_READ) transaction isolation levels.
An internal materialized view table is initially in an invalid state (there is no data). The first user query will trigger an implicit loading of the data. All other queries against the materialized view will block until the load completes. In some situations administrators may wish to better control when the cache is loaded with a call to SYSADMIN.refreshMatView. The initial load may itself trigger the initial load of dependent materialized views. After the initial load user queries against the materialized view table will only block if it is in an invalid state. The valid state may also be controlled through the SYSADMIN.refreshMatView procedure.
CALL SYSADMIN.refreshMatView(viewname=>'schema.matview', invalidate=>true)
While the initial load may trigger a transitive loading of dependent materialized views, subsequent refreshes performed with refreshMatView will use dependent materialized view tables if they exist. Only one load may occur at a time. If a load is already in progress when the SYSADMIN.refreshMatView procedure is called, it will return -1 immediately rather than preempting the current load.
The Cache Hint may be used to automatically trigger a full snapshot refresh after a specified time to live (ttl). The ttl starts from the time the table is finished loading. The refresh is equivalent to CALL SYSADMIN.refreshMatView('view name', false), but performed asynchronously so that user queries do not block on the load.
/*+ cache(ttl:3600000) */ select t.col, t1.col from t, t1 where t.id = t1.id
The resulting materialized view will be reloaded every hour (3600000 milliseconds).
The automatic ttl refresh is not intended for complex loading scenarios, as nested materialized views will be used by the refresh query.
The automatic ttl refresh is performed lazily, that is it is only trigger by using the table after the ttl has expired. For infrequently used tables with long load times, this means that data may be used well past the intended ttl.
Teiid exposes bean that implements org.teiid.events.EventDistributor interface, under JNDI name teiid/event-distributor-factory in the JBoss AS VM that can looked up in the JNDI tree and invoke methods like dataModification or updateMatViewRow to alert the Teiid engine that the underlying source data has been modified. These operations, which work cluster wide will invalidate the cache entries appropriately and reload the new cache contents.
If your source system has any built-in change data capture facilities that can scrape logs, install triggers, etc. to capture data change events, they can captured and can be propagated to Teiid engine through a pojo bean/MDB/Session Bean deployed in JBoss AS engine.
The below shows a code example as how user can use EventDistributor interface in their own code that is deployed in the same JBoss AS VM using a Pojo/MDB/Session Bean. Consult JBoss AS documents deploying as bean as they out of scope for this document.
public class ChanageDataCapture {
public void invalidate() {
InitialContext ic = new InitialContext();
EventDistributor ed = (EventDistributor)ic.lookup("teiid/event-distributor-factory");
// this below line indicates that Customer table in the "model-name" schema has been changed.
// this result in cache reload.
ed.dataModification("vdb-name", "version", "model-name", "Customer");
}
}
The EventDistributor interface also exposes many methods that can be used to update the costing information on your source models for optimized query planning. Note that these values volatile and will be lost during a cluster re-start, as there is no repository to persist.
In advanced use-cases the cache hint may also be used to mark an internal materialized view as updatable. An updatable internal materialized view may use the SYSADMIN.refreshMatViewRow procedure to update a single row in the materialized table. If the source row exists, the materialized view table row will be updated. If the source row does not exist, the correpsonding materialized row will be deleted. To be updatable the materialized view must have a single column primary key. Composite keys are not yet supported by SYSADMIN.refreshMatViewRow. Transformation Query:
/*+ cache(updatable) */ select t.col, t1.col from t, t1 where t.id = t1.id
Update SQL:
CALL SYSADMIN.updateMatViewRow(viewname=>'schema.matview', key=>5)
Given that the schema.matview defines an integer column col as its primary key, the update will check the live source(s) for the row values.
The update query will not use dependent materialized view tables, so care should be taken to ensure that getting a single row from this transformation query performs well. See the Reference Guide for information on controlling dependent joins, which may be applicable to increasing the performance of retrieving a single row. The refresh query does use nested caches, so this refresh method should be used with caution.
When the updatable option is not specified, accessing the materialized view table is more efficient because modifications do not need to be considered. Therefore, only specify the updatable option if row based incremental updates are needed. Even when performing row updates, full snapshot refreshes may be needed to ensure consistency.
Internal materialized view tables will automatically create non-unique indexes for each unique constraint and index defined on the materialized view. These indexes are created as non-unique even for unique constraints since the materialized table is not intended as an enforcement point for data integrity and when updatable the table may not be consistent with underlying values and thus unable to satisfy constraints. The primary key (if it exists) of the view will automatically be part of the covered columns for the index.
The secondary indexes are always created as trees - bitmap or hash indexes are not supported. Teiid's metadata for indexes is currently limited. We are not currently able to capture additional information, sort direction, additional columns to cover, etc. You may workaround some of these limitations though.
Function based index are supported, but can only be specified through DDL metadata. If you are not using DDL metadata, consider adding another column to the view that projects the function expression, then place an index on that new column. Queries to the view will need to be modified as appropriate though to make use of the new column/index.
If additional covered columns are needed, they may simply be added to the index columns. This however is only applicable to comparable types. Adding additional columns will increase the amount of space used by the index, but may allow its usage to result in higher performance when only the covered columns are used and the main table is not consulted.
Each member in a cluster maintains its own copy of each materialized table and associated indexes. An attempt is made to ensure each member receives the same full refresh events as the others. Full consistency for updatable materialized views however is not guaranteed. Periodic full refreshes of updatable materialized view tables helps ensure consistency among members.